﻿

-- 用户权限变更信息
CREATE view [dbo].[vwFetchChangeUserAuthority]
as
-- 新增
select JsdRyqx_Info.jsdbh,sfzh,sblsh,0 zt
from JsdRyqx_Info
inner join Jsdsb_Info on(JsdRyqx_Info.jsdbh=Jsdsb_Info.jsdbh)
except
select jsdbh,sfzh,sblsh,0 zt
from SbDqyh_Info
union
-- 删除
select jsdbh,sfzh,sblsh,2 zt
from SbDqyh_Info
except
select JsdRyqx_Info.jsdbh,sfzh,sblsh,2 zt
from JsdRyqx_Info
inner join Jsdsb_Info on(JsdRyqx_Info.jsdbh=Jsdsb_Info.jsdbh)
union
-- 权限变更
select JsdRyqx_Info.jsdbh,JsdRyqx_Info.sfzh,Jsdsb_Info.sblsh,1 zt
from JsdRyqx_Info
inner join Jsdsb_Info on(JsdRyqx_Info.jsdbh=Jsdsb_Info.jsdbh)
inner join SbDqyh_Info on(JsdRyqx_Info.jsdbh=SbDqyh_Info.jsdbh 
and JsdRyqx_Info.sfzh=SbDqyh_Info.sfzh and Jsdsb_Info.sblsh=SbDqyh_Info.sblsh)
where
JsdRyqx_Info.kh<>SbDqyh_Info.kh or JsdRyqx_Info.sfhbmd<>SbDqyh_Info.sfhbmd or 
JsdRyqx_Info.sfkqfq<>SbDqyh_Info.sfkqfq or isnull(JsdRyqx_Info.yxjcsj,'2100-01-01')<>isnull(SbDqyh_Info.yxjcsj,'2100-01-01')